﻿Imports Excel
Imports System.Windows.Forms

Public Class EnglishExcelCreate
    Inherits ExcelCreateBase

    Public Event PageComplete(ByVal page As Integer, ByVal totalPage As Integer)
    Public Event PrintProcess(ByVal content As String)

    'Private Const PAGECOUNT = 39
    Public Sub New(ByVal fileName As String)
        MyBase.New(fileName)
    End Sub

    Private Sub PageSetUp()
        With Me.GetCurrentWorkSheet.PageSetup
            .LeftMargin = CentimetersToPoints(0.2)
            .RightMargin = CentimetersToPoints(0.2)
            .TopMargin = CentimetersToPoints(0.2)
            .BottomMargin = CentimetersToPoints(0.2)
            .HeaderMargin = CentimetersToPoints(0.2)
            .FooterMargin = CentimetersToPoints(0.2)
            .PrintComments = Excel.XlPrintLocation.xlPrintNoComments
            .CenterHorizontally = True
            '.CenterVertically = True
            .Orientation = Excel.XlPageOrientation.xlLandscape
        End With
    End Sub

    Private Sub SetTemp(ByVal page As Integer)
        With Me.GetCurrentWorkSheet
            .Range("A" & CStr((page - 1) * 报表记录条数字 + 1)).Select()
            .Paste()
        End With

        If page Mod 5 = 0 Then
            RaiseEvent PrintProcess("正在设置第" & page & "页摸版")
        End If

    End Sub

    Private Sub SetTemp()
        With Me.GetCurrentWorkSheet
            '设置列宽
            .Columns.Item("A:A", Type.Missing).ColumnWidth = 13
            .Columns.Item("B:B", Type.Missing).ColumnWidth = 31.75

            .Columns.Item("C:C", Type.Missing).ColumnWidth = 13
            .Columns.Item("D:D", Type.Missing).ColumnWidth = 31.75

            .Columns.Item("E:E", Type.Missing).ColumnWidth = 13
            .Columns.Item("F:F", Type.Missing).ColumnWidth = 31.75
           
            ''设置标题背景边框
            'With .Range("A1:F1")
            '    .Interior.ColorIndex = 15
            '    With .Font
            '        .Bold = True
            '        .Name = 报表字体
            '        .Size = 12
            '        .Strikethrough = False
            '        .Superscript = False
            '        .Subscript = False
            '        .OutlineFont = False
            '        .Shadow = False
            '        .Underline = -4142
            '    End With
            'End With

            With .Range("A1:F" & 报表记录条数字)

                With .Font
                    .Name = 报表字体
                End With

                With .Borders(Excel.XlBordersIndex.xlEdgeLeft)
                    .LineStyle = XlLineStyle.xlContinuous
                    .Weight = XlBorderWeight.xlThin
                    .ColorIndex = -4105
                End With
                With .Borders(Excel.XlBordersIndex.xlEdgeTop)
                    .LineStyle = XlLineStyle.xlContinuous
                    .Weight = XlBorderWeight.xlThin
                    .ColorIndex = -4105
                End With
                With .Borders(Excel.XlBordersIndex.xlEdgeBottom)
                    .LineStyle = XlLineStyle.xlContinuous
                    .Weight = XlBorderWeight.xlThin
                    .ColorIndex = -4105
                End With
                With .Borders(Excel.XlBordersIndex.xlEdgeRight)
                    .LineStyle = XlLineStyle.xlContinuous
                    .Weight = XlBorderWeight.xlThin
                    .ColorIndex = -4105
                End With
                With .Borders(Excel.XlBordersIndex.xlInsideVertical)
                    .LineStyle = XlLineStyle.xlContinuous
                    .Weight = XlBorderWeight.xlThin
                    .ColorIndex = -4105
                End With
                With .Borders(Excel.XlBordersIndex.xlInsideHorizontal)
                    .LineStyle = XlLineStyle.xlContinuous
                    .Weight = XlBorderWeight.xlThin
                    .ColorIndex = -4105
                End With
            End With
            With .Range("A1:F" & 报表记录条数字)
                .Copy()
            End With
        End With
    End Sub

    Private Sub PrintHeader()

    End Sub

    Private Sub PrintContent(ByVal dt As System.Data.DataTable)
        Dim page As Integer = Fix(dt.Rows.Count / ((报表记录条数字 - 1) * 3))
        If dt.Rows.Count Mod ((报表记录条数字 - 1) * 3) <> 0 Then
            page += 1
        End If

        Dim sb As New System.Text.StringBuilder
        For index As Integer = 1 To page
            'sb.AppendLine("单词" & vbTab & "释义" & vbTab & "单词" & vbTab & "释义" & vbTab & "单词" & vbTab & "释义")

            Dim startRow As Integer = (index - 1) * ((报表记录条数字 - 1) * 3)
            For jdex As Integer = 1 To 报表记录条数字 - 1
                With Me.GetCurrentWorkSheet
                    startRow += 1
                    If startRow > dt.Rows.Count Then
                        Exit For
                    End If
                    sb.Append(CStr(dt.Rows(startRow - 1)("WORD")).Trim)
                    sb.Append(vbTab)
                    sb.Append(CStr(dt.Rows(startRow - 1)("CHINESEWORD")).Trim)

                    startRow += 1
                    If startRow > dt.Rows.Count Then
                        Exit For
                    End If
                    sb.Append(vbTab)
                    sb.Append(CStr(dt.Rows(startRow - 1)("WORD")).Trim)
                    sb.Append(vbTab)
                    sb.Append(CStr(dt.Rows(startRow - 1)("CHINESEWORD")).Trim)

                    startRow += 1
                    If startRow > dt.Rows.Count Then
                        Exit For
                    End If
                    sb.Append(vbTab)
                    sb.Append(CStr(dt.Rows(startRow - 1)("WORD")).Trim)
                    sb.Append(vbTab)
                    sb.AppendLine(CStr(dt.Rows(startRow - 1)("CHINESEWORD")).Trim)
                End With
            Next

            If index Mod 5 = 0 Then
                RaiseEvent PrintProcess("正在打印第" & index & "页内容")
            End If

        Next

        Dim con As String = sb.ToString.Trim
        If con.Length > 0 Then
            With Me.GetCurrentWorkSheet
                Clipboard.SetDataObject(con)
                .Range("A1").Select()
                .Paste()
            End With
        End If
    End Sub

    Public Sub DoOutData(ByVal dt As System.Data.DataTable)

        '打开Excel
        RaiseEvent PrintProcess("正在打开Excel")
        打开()

        '进行页面设置
        RaiseEvent PrintProcess("正在页面设置")
        PageSetUp()

        '每页打印90个单词
        RaiseEvent PrintProcess("正在计算打印页数")
        Dim page As Integer = Fix(dt.Rows.Count / ((报表记录条数字 - 1) * 3))
        If dt.Rows.Count Mod ((报表记录条数字 - 1) * 3) <> 0 Then
            page += 1
        End If

        RaiseEvent PrintProcess("共" & page & "页")
        For index As Integer = 1 To page
            '第一页 先设置好做模版
            If index = 1 Then
                RaiseEvent PrintProcess("正在初始化模版")
                SetTemp()
            Else
                SetTemp(index)
            End If
        Next

        RaiseEvent PrintProcess("正在打印内容")
        PrintContent(dt)

        RaiseEvent PrintProcess("正在保存......")
        保存()

        RaiseEvent PrintProcess("正在关闭......")
        关闭()

        RaiseEvent PrintProcess("")
    End Sub

#Region "报表必须使用"
    Private _报表字体 As String = "宋体"
    Public Overrides Property 报表字体() As String
        Get
            Return _报表字体
        End Get
        Set(ByVal value As String)
            _报表字体 = value
        End Set
    End Property

    Private _报表记录条数字 As Integer = 39
    Public Overrides Property 报表记录条数字() As Integer
        Get
            Return _报表记录条数字
        End Get
        Set(ByVal value As Integer)
            _报表记录条数字 = value
        End Set
    End Property
#End Region

End Class
